﻿Public Class clsDbTenkenList
    Public Function GetTenkenData(ByVal szEquipID As String, ByVal szTag As String) As String
        Dim szSql As String = ""
        szSql &= "SELECT DATAVAL "
        szSql &= "FROM PER.L_TENKENLIST "
        szSql &= "WHERE SETUBINO = '" & szEquipID & "' "
        szSql &= "AND TAGNM = '" & szTag & "' "
        szSql &= "AND SHEETNM = 'Monitoring' "
        szSql &= "AND TENKENNO = 0 "
        szSql &= "ORDER BY SYS_CMD_ID DESC "

        Dim dt As DataTable = DBacs.ExecuteSql(szSql)
        If IsNothing(dt) Then
            GetTenkenData = String.Empty
        ElseIf dt.Rows.Count > 0 Then
            GetTenkenData = dt.Rows(0)(0)
        Else
            GetTenkenData = String.Empty
        End If
    End Function

    Public Function GetLastTenkenNo(ByVal szSheetNm As String) As Integer
        Dim szSql As String = ""
        szSql &= "SELECT COALESCE(MAX(TENKENNO) + 1, 1) AS COUNT "
        szSql &= "FROM PER.L_TENKENLIST "
        szSql &= "WHERE 1=1 "
        szSql &= "AND SHEETNM='" & szSheetNm & "' "
        szSql &= "AND TENKENYEAR=(TO_CHAR(NOW(), 'YYYY'), '9999' ) "
        szSql &= "AND TENKENMONTH=(TO_CHAR(NOW(), 'MM'), '99') "
        Return CInt(DBacs.ExccuteScalarSql(szSql, -1))
    End Function

    Public Function SaveSwapDB(ByVal lstData As List(Of String()), ByVal szEquipID As String, ByVal szLotNo As String, ByVal szOpt As String) As Integer
        DBacs.BeginTransaction()

        Dim szSql As String = ""
        
        Dim iNo As Integer = GetLastTenkenNo("モールド作業稼動帳")

        szSql = ""
        szSql &= "INSERT INTO PER.L_TENKENLIST(  "
        szSql &= "  SYUBETUNO, SETUBINO, LOTID, SHEETNM, TANTOUNM, "
        szSql &= "  HANTEISEL, HANTEIVAL, TAGNM, DATAVAL, "
        szSql &= "  TENKENYEAR, TENKENMONTH, TENKENDAY, TENKENNO "
        szSql &= ") "
        szSql &= "VALUES (  "
        szSql &= "  0, '{0}', '{1}', 'DB品種交換チェックシート', '{2}', "
        szSql &= "  '{3}', '{4}', '{5}', '{6}', "
        szSql &= "  TO_CHAR(NOW(), 'YYYY'), TO_CHAR(NOW(), 'MM'), TO_CHAR(NOW(), 'DD'), "
        szSql &= "  " & iNo & " "
        szSql &= ") "

        'Dim dteNow As Date = DateTime.Now
        'Dim iYear As Integer = dteNow.Year
        'Dim iMonth As Integer = dteNow.Month
        'Dim iDay As Integer = dteNow.Day

        Dim iSum As Integer = 0
        For Each szData() As String In lstData
            Dim szSqlEx As String = _
                String.Format(szSql, szEquipID, szLotNo, szOpt, _
                szData(2), szData(3), szData(4), szData(5))
            Dim iCnt = DBacs.ExecuteUpdateSql(szSqlEx, -1)
            If iCnt < 0 Then
                DBacs.RollbackTransaction()
                Return iCnt
            Else
                iSum += iCnt
            End If
        Next

        DBacs.CommitTransaction()
        Return iSum
    End Function



    Public Function SaveSwapMold(ByVal lstData As List(Of String()), ByVal szEquipID As String, ByVal szLotNo As String, ByVal szOpt As String) As Integer
        DBacs.BeginTransaction()

        Dim szSql As String = ""

        Dim iNo As Integer = GetLastTenkenNo("モールド作業稼動帳")

        szSql = ""
        szSql &= "INSERT INTO PER.L_TENKENLIST(  "
        szSql &= "  SYUBETUNO, SETUBINO, LOTID, SHEETNM, TANTOUNM, "
        szSql &= "  TAGNM, DATAVAL, "
        szSql &= "  TENKENYEAR, TENKENMONTH, TENKENDAY, TENKENNO "
        szSql &= ") "
        szSql &= "VALUES (  "
        szSql &= "  0, '{0}', '{1}', 'ﾓｰﾙﾄﾞ条件確認票', '{2}', "
        szSql &= "  '{3}', '{4}', "
        szSql &= "  TO_CHAR(NOW(), 'YYYY'), TO_CHAR(NOW(), 'MM'), TO_CHAR(NOW(), 'DD'), "
        szSql &= "  " & iNo & " "
        szSql &= ") "

        'Dim dteNow As Date = DateTime.Now
        'Dim iYear As Integer = dteNow.Year
        'Dim iMonth As Integer = dteNow.Month
        'Dim iDay As Integer = dteNow.Day

        Dim iSum As Integer = 0
        For Each szData() As String In lstData
            Dim szSqlEx As String = _
                String.Format(szSql, szEquipID, szLotNo, szOpt, _
                szData(2), szData(3))
            Dim iCnt = DBacs.ExecuteUpdateSql(szSqlEx, -1)
            If iCnt < 0 Then
                DBacs.RollbackTransaction()
                Return iCnt
            Else
                iSum += iCnt
            End If
        Next

        DBacs.CommitTransaction()
        Return iSum
    End Function

    'Public Function GetMoldProdCond(ByVal szEquipID As String) As DataTable
    '    Dim szSql As String = ""
    '    szSql &= "SELECT MAT.NUM, MAT.PROCS, MAT.LOT_NO, MAT.PKG, "
    '    szSql &= "  COALESCE(LST.ProdPin, '') AS ProdPin, "
    '    szSql &= "  COALESCE(LST.ProdCond1, '') AS ProdCond1, "
    '    szSql &= "  COALESCE(LST.ProdCond2, '') AS ProdCond2, "
    '    szSql &= "  COALESCE(LST.ProdCond3, '') AS ProdCond3, "
    '    szSql &= "  COALESCE(LST.ProdCond4, '') AS ProdCond4, "
    '    szSql &= "  COALESCE(LST.ProdInjecFlg, '') AS ProdInjecFlg, "
    '    szSql &= "  MAT.begin_time "
    '    szSql &= "FROM ( "
    '    szSql &= "  SELECT 1 AS NUM, '製品着工' AS PROCS, "
    '    szSql &= "    MOLD_TOOL AS PKG, LOT_NO,"
    '    szSql &= "    EQUIP_ID, BEGIN_TIME  "
    '    szSql &= "  FROM MAT.L_EQUIP_MAT_MOLD "
    '    szSql &= "   WHERE IS_CONDITION=TRUE "
    '    szSql &= "  ORDER BY SYS_CMD_ID DESC "
    '    szSql &= "  LIMIT 6 "
    '    szSql &= ") MAT "
    '    szSql &= "LEFT OUTER JOIN ("
    '    szSql &= "  SELECT "
    '    szSql &= "     ARRAY_TO_STRING(ARRAY_AGG(CASE WHEN TAGNM='ProdPin' THEN DATAVAL ELSE '' END),'') AS ProdPin "
    '    szSql &= "    ,ARRAY_TO_STRING(ARRAY_AGG(CASE WHEN TAGNM='ProdCond1' THEN DATAVAL ELSE '' END),'') AS ProdCond1 "
    '    szSql &= "    ,ARRAY_TO_STRING(ARRAY_AGG(CASE WHEN TAGNM='ProdCond2' THEN DATAVAL ELSE '' END),'') AS ProdCond2 "
    '    szSql &= "    ,ARRAY_TO_STRING(ARRAY_AGG(CASE WHEN TAGNM='ProdCond3' THEN DATAVAL ELSE '' END),'') AS ProdCond3 "
    '    szSql &= "    ,ARRAY_TO_STRING(ARRAY_AGG(CASE WHEN TAGNM='ProdCond4' THEN DATAVAL ELSE '' END),'') AS ProdCond4 "
    '    szSql &= "    ,ARRAY_TO_STRING(ARRAY_AGG(CASE WHEN TAGNM='ProdInjecFlg' THEN DATAVAL ELSE '' END),'') AS ProdInjecFlg "
    '    szSql &= "    ,SETUBINO, LOT_NO, TENKENNO "
    '    szSql &= "  FROM ( "
    '    szSql &= "    SELECT DATAVAL, TAGNM, SETUBINO, LOTID AS LOT_NO, SYS_CMD_ID, TENKENNO "
    '    szSql &= "    FROM PER.L_TENKENLIST "
    '    szSql &= "    WHERE LOTID IN ( "
    '    szSql &= "      SELECT LOT_NO "
    '    szSql &= "      FROM MAT.L_EQUIP_MAT_MOLD "
    '    szSql &= "      WHERE IS_CONDITION=TRUE "
    '    szSql &= "      ORDER BY SYS_CMD_ID DESC "
    '    szSql &= "      LIMIT 6"
    '    szSql &= "    )"
    '    szSql &= "    AND SETUBINO='" & szEquipID & "'"
    '    szSql &= "    AND SHEETNM = 'モールド作業稼動帳' "
    '    szSql &= "    AND TENKENYEAR=TO_NUMBER(TO_CHAR(NOW(), 'YYYY'), '9999' ) "
    '    szSql &= "    AND TENKENMONTH=TO_NUMBER(TO_CHAR(NOW(), 'MM'), '99') "
    '    szSql &= "    AND TENKENNO IN ( "
    '    szSql &= "      SELECT MAX(TENKENNO) FROM PER.L_TENKENLIST "
    '    szSql &= "      WHERE LOTID IN ( "
    '    szSql &= "        SELECT LOT_NO "
    '    szSql &= "        FROM MAT.L_EQUIP_MAT_MOLD "
    '    szSql &= "        WHERE IS_CONDITION=TRUE "
    '    szSql &= "        ORDER BY SYS_CMD_ID DESC "
    '    szSql &= "        LIMIT 6 "
    '    szSql &= "      ) "
    '    szSql &= "      GROUP BY LOTID "
    '    szSql &= "    ) "
    '    szSql &= "  ) T0 "
    '    szSql &= "  GROUP BY SETUBINO, LOT_NO, TENKENNO "
    '    szSql &= ") LST "
    '    szSql &= "ON LST.LOT_NO=MAT.LOT_NO "
    '    szSql &= "AND LST.SETUBINO=MAT.EQUIP_ID "
    '    Dim dt1 As DataTable = DBacs.ExecuteSql(szSql)
    '    If IsNothing(dt1) Then
    '        Return New DataTable
    '    End If

    '    szSql = ""
    '    szSql &= "  SELECT TENKENNO "
    '    szSql &= "    ,ARRAY_TO_STRING(ARRAY_AGG(CASE WHEN TAGNM='Procs' THEN DATAVAL ELSE '' END),'') AS Procs "
    '    szSql &= "    ,LOTID AS LOT_NO "
    '    szSql &= "    ,ARRAY_TO_STRING(ARRAY_AGG(CASE WHEN TAGNM='ProdPkg' THEN DATAVAL ELSE '' END),'') AS ProdPkg "
    '    szSql &= "    ,ARRAY_TO_STRING(ARRAY_AGG(CASE WHEN TAGNM='ProdPin' THEN DATAVAL ELSE '' END),'') AS ProdPin "
    '    szSql &= "    ,ARRAY_TO_STRING(ARRAY_AGG(CASE WHEN TAGNM='ProdCond1' THEN DATAVAL ELSE '' END),'') AS ProdCond1 "
    '    szSql &= "    ,ARRAY_TO_STRING(ARRAY_AGG(CASE WHEN TAGNM='ProdCond2' THEN DATAVAL ELSE '' END),'') AS ProdCond2 "
    '    szSql &= "    ,ARRAY_TO_STRING(ARRAY_AGG(CASE WHEN TAGNM='ProdCond3' THEN DATAVAL ELSE '' END),'') AS ProdCond3 "
    '    szSql &= "    ,ARRAY_TO_STRING(ARRAY_AGG(CASE WHEN TAGNM='ProdCond4' THEN DATAVAL ELSE '' END),'') AS ProdCond4 "
    '    szSql &= "    ,ARRAY_TO_STRING(ARRAY_AGG(CASE WHEN TAGNM='ProdInjecFlg' THEN DATAVAL ELSE '' END),'') AS ProdInjecFlg "
    '    szSql &= "    ,SAVEDATE "
    '    szSql &= "  FROM PER.L_TENKENLIST "
    '    szSql &= "  WHERE SETUBINO='" & szEquipID & "' "
    '    szSql &= "  AND SHEETNM = 'モールド作業稼動帳'  "
    '    szSql &= "  AND TENKENYEAR=TO_NUMBER(TO_CHAR(NOW(), 'YYYY'), '9999' ) "
    '    szSql &= "  AND TENKENMONTH=TO_NUMBER(TO_CHAR(NOW(), 'MM'), '99') "
    '    szSql &= "  GROUP BY TENKENNO, LOT_NO, SAVEDATE "

    '    Dim i As Integer
    '    Dim dt2 As DataTable = DBacs.ExecuteSql(szSql)
    '    If Not IsNothing(dt2) AndAlso dt2.Rows.Count > 0 Then
    '        For Each dr As DataRow In dt2.Rows
    '            Dim drNew As DataRow = dt1.NewRow
    '            For i = 0 To dt2.Columns.Count - 1
    '                drNew(i) = dr(i)
    '            Next
    '            dt1.Rows.Add(drNew)
    '        Next
    '    End If

    '    Dim dt As DataTable = dt1.Clone
    '    Dim drSort() As DataRow = dt1.Select("", "begin_time")
    '    For i = 0 To drSort.Length - 1
    '        If i = 6 Then
    '            Exit For
    '        End If
    '        dt.ImportRow(drSort(i))
    '    Next


    '    GetMoldProdCond = dt
    'End Function

    Public Function GetMoldProdResin(ByVal szEquipID As String) As DataTable
        Dim dt As DataTable = GetMoldProdHis(szEquipID, {"mold_limit_time", "mold_resin", "mold_resin_lot"}, _
                                           "    ,ARRAY_TO_STRING(ARRAY_AGG(CASE WHEN TAGNM='ProdResin' THEN DATAVAL ELSE '' END),'') AS ProdResin " & _
                                           "    ,ARRAY_TO_STRING(ARRAY_AGG(CASE WHEN TAGNM='ProdResinLot' THEN DATAVAL ELSE '' END),'') AS ProdResinLot ", _
                                           "ProdResinBox", "ProdFilm")
        Return dt
    End Function

    Public Function GetMoldProdCond(ByVal szEquipID As String) As DataTable
        Dim dt As DataTable = GetMoldProdHis(szEquipID, {"MOLD_TOOL"}, _
                                           "    ,ARRAY_TO_STRING(ARRAY_AGG(CASE WHEN TAGNM='ProdPkg' THEN DATAVAL ELSE '' END),'') AS ProdPkg ", _
                                           "ProdPin", "ProdCond1", "ProdCond2", "ProdCond3", "ProdCond4", "ProdInjecFlg")
        Return dt
    End Function

    Public Function GetMoldProdCh(ByVal szEquipID As String) As DataTable
        Dim dt As DataTable = GetMoldProdHis(szEquipID, {"TOTAL_TIME"}, _
                                           "    ,ARRAY_TO_STRING(ARRAY_AGG(CASE WHEN TAGNM='ProdPkg' THEN DATAVAL ELSE '' END),'') AS ProdPkg ", _
                                           "ProdApprChk1", "ProdApprChk2", "ProdApprChk3", "ProdApprChk4", "ProdCelRp", "ProdDummy", "ProdApprChk", "Prod1Appr", "Prod1Xray")
        Return dt
    End Function

    Public Function GetMoldProdMemo(ByVal szEquipID As String) As DataTable
        Dim dt As DataTable = GetMoldProdHis(szEquipID, {}, _
                                           "", _
                                           "ProdShot", "ProdMemo")
        Return dt
    End Function

    Private Function GetMoldProdHis(ByVal szEquipID As String, ByVal szMats() As String, ByVal szLst As String, ByVal ParamArray szItems() As String) As DataTable
        Dim szSql As String = ""
        szSql &= "SELECT MAT.NUM, MAT.PROCS, MAT.LOT_NO, "
        For Each mat As String In szMats
            szSql &= String.Format("MAT.{0},", mat)
        Next
        For Each item As String In szItems
            szSql &= String.Format("  COALESCE(LST.{0}, '') AS {0}, ", item)
        Next
        szSql &= "  MAT.begin_time "
        szSql &= "FROM ( "
        szSql &= "  SELECT 1 AS NUM, '製品着工' AS PROCS, LOT_NO, "
        For Each mat As String In szMats
            szSql &= String.Format("{0}, ", mat)
        Next
        szSql &= "    EQUIP_ID, BEGIN_TIME  "
        szSql &= "  FROM MAT.L_EQUIP_MAT_MOLD "
        szSql &= "   WHERE IS_CONDITION=TRUE "
        szSql &= "  ORDER BY SYS_CMD_ID DESC "
        szSql &= "  LIMIT 6 "
        szSql &= ") MAT "
        szSql &= "LEFT OUTER JOIN ("
        szSql &= "  SELECT "
        For Each item As String In szItems
            szSql &= String.Format("     ARRAY_TO_STRING(ARRAY_AGG(CASE WHEN TAGNM='{0}' THEN DATAVAL ELSE '' END),'') AS {0}, ", item)
        Next
        szSql &= "    SETUBINO, LOT_NO, TENKENNO "
        szSql &= "  FROM ( "
        szSql &= "    SELECT DATAVAL, TAGNM, SETUBINO, LOTID AS LOT_NO, SYS_CMD_ID, TENKENNO "
        szSql &= "    FROM PER.L_TENKENLIST "
        szSql &= "    WHERE LOTID IN ( "
        szSql &= "      SELECT LOT_NO "
        szSql &= "      FROM MAT.L_EQUIP_MAT_MOLD "
        szSql &= "      WHERE IS_CONDITION=TRUE "
        szSql &= "      ORDER BY SYS_CMD_ID DESC "
        szSql &= "      LIMIT 6"
        szSql &= "    )"
        szSql &= "    AND SETUBINO='" & szEquipID & "'"
        szSql &= "    AND SHEETNM = 'モールド作業稼動帳' "
        szSql &= "    AND TENKENYEAR=TO_NUMBER(TO_CHAR(NOW(), 'YYYY'), '9999' ) "
        szSql &= "    AND TENKENMONTH=TO_NUMBER(TO_CHAR(NOW(), 'MM'), '99') "
        szSql &= "    AND TENKENNO IN ( "
        szSql &= "      SELECT MAX(TENKENNO) FROM PER.L_TENKENLIST "
        szSql &= "      WHERE LOTID IN ( "
        szSql &= "        SELECT LOT_NO "
        szSql &= "        FROM MAT.L_EQUIP_MAT_MOLD "
        szSql &= "        WHERE IS_CONDITION=TRUE "
        szSql &= "        ORDER BY SYS_CMD_ID DESC "
        szSql &= "        LIMIT 6 "
        szSql &= "      ) "
        szSql &= "      GROUP BY LOTID "
        szSql &= "    ) "
        szSql &= "  ) T0 "
        szSql &= "  GROUP BY SETUBINO, LOT_NO, TENKENNO "
        szSql &= ") LST "
        szSql &= "ON LST.LOT_NO=MAT.LOT_NO "
        szSql &= "AND LST.SETUBINO=MAT.EQUIP_ID "
        Dim dt1 As DataTable = DBacs.ExecuteSql(szSql)
        If IsNothing(dt1) Then
            Return New DataTable
        End If

        'If szMats.Length > 0 Then
        szSql = ""
        szSql &= "  SELECT TENKENNO "
        szSql &= "    ,ARRAY_TO_STRING(ARRAY_AGG(CASE WHEN TAGNM='Procs' THEN DATAVAL ELSE '' END),'') AS Procs "
        szSql &= "    ,LOTID "
        szSql &= szLst
        For Each item As String In szItems
            szSql &= String.Format("    ,ARRAY_TO_STRING(ARRAY_AGG(CASE WHEN TAGNM='{0}' THEN DATAVAL ELSE '' END),'') AS {0} ", item)
        Next
        szSql &= "    ,SAVEDATE "
        szSql &= "  FROM PER.L_TENKENLIST "
        szSql &= "  WHERE SETUBINO='" & szEquipID & "' "
        szSql &= "  AND SHEETNM = 'モールド作業稼動帳'  "
        szSql &= "  AND TENKENYEAR=TO_NUMBER(TO_CHAR(NOW(), 'YYYY'), '9999' ) "
        szSql &= "  AND TENKENMONTH=TO_NUMBER(TO_CHAR(NOW(), 'MM'), '99') "
        szSql &= "  GROUP BY TENKENNO, LOTID, SAVEDATE "

        Dim i As Integer
        Dim dt2 As DataTable = DBacs.ExecuteSql(szSql)
        If Not IsNothing(dt2) AndAlso dt2.Rows.Count > 0 Then
            For Each dr As DataRow In dt2.Rows
                Dim drNew As DataRow = dt1.NewRow
                For i = 0 To dt2.Columns.Count - 1
                    drNew(i) = dr(i)
                Next
                dt1.Rows.Add(drNew)
            Next
        End If
        'End If

        Dim dt As DataTable = dt1.Clone
        Dim drSort() As DataRow = dt1.Select("", "begin_time")
        For i = 0 To drSort.Length - 1
            If i = 6 Then
                Exit For
            End If
            dt.ImportRow(drSort(i))
        Next


        GetMoldProdHis = dt
    End Function


    Private Function EquipMatMoldSQL() As String
        Dim szSql As String = ""
        szSql &= "SELECT MAT.NUM, MAT.PROCS, MAT.LOT_NO, MAT.PKG, "
        szSql &= "  COALESCE(LST.ProdPin, '') AS ProdPin, "
        szSql &= "  COALESCE(LST.ProdCond1, '') AS ProdCond1, "
        szSql &= "  COALESCE(LST.ProdCond2, '') AS ProdCond2, "
        szSql &= "  COALESCE(LST.ProdCond3, '') AS ProdCond3, "
        szSql &= "  COALESCE(LST.ProdCond4, '') AS ProdCond4, "
        szSql &= "  COALESCE(LST.ProdInjecFlg, '') AS ProdInjecFlg, "
        szSql &= "  MAT.begin_time "
        szSql &= "FROM ( "
        szSql &= "  SELECT 1 AS NUM, '製品着工' AS PROCS, "
        szSql &= "    MOLD_TOOL AS PKG, LOT_NO,"
        szSql &= "    EQUIP_ID, BEGIN_TIME  "
        szSql &= "  FROM MAT.L_EQUIP_MAT_MOLD "
        szSql &= "   WHERE IS_CONDITION=TRUE "
        szSql &= "  ORDER BY SYS_CMD_ID DESC "
        szSql &= "  LIMIT 6 "
        szSql &= ") MAT "

        Return szSql
    End Function
End Class
